﻿using System;
using System.Collections.Generic;
using System.Reflection;
using System.Data.SqlClient;
using Common.ADO.Tools;

namespace Common.Reflection.DAL.Managers
{
    /// <summary>
    /// 反射数据库的添删改查基本类
    /// </summary>
    public class WinFormSQLManager //: Reflection.DAL.InterFace.DBInterFace
    {

        /// <summary>
        /// 返回LogeManager对象并初始化
        /// </summary>
        /// <returns></returns>
        public static WinFormSQLManager GetInstance()
        {
            WinFormSQLManager lm = new WinFormSQLManager();
            return lm;
        }

        private WinFormSQLManager() { }

        /// <summary>
        /// 查询返回表里的所有记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public List<T> GetList<T>() where T : class, new()
        {
            //List<T> tList = new List<T>();
            //Type type = typeof(T);
            //string sql = string.Format("select * from {0}", Entity.GetTableName<T>());
            //SqlDataReader reader = WinFormDbHelperSQL.ExecuteReader(sql);
            //while (reader.Read())
            //{
            //    T t = new T();
            //    PropertyInfo[] properties = type.GetProperties();
            //    foreach (PropertyInfo p in properties)
            //    {
            //        string columnName = p.Name;
            //        string PType = p.PropertyType.ToString();
            //        DBHelper.Operator(p, PType, reader[columnName], t, 0);
            //    }
            //    tList.Add(t);
            //}
            //reader.Close();

            //return tList;
            return null;
        }

        /// <summary>
        /// 分页返回表里的所有记录
        /// </summary>
        /// <typeparam name="T">对象类型</typeparam>
        /// <param name="PageSize">每页信息量</param>
        /// <param name="NowPage">当前页</param>
        /// <returns></returns>
        public List<T> GetListByPage<T>(int PageSize, int NowPage, out int PageCount, out int recordCount) where T : class, new()
        {
            //List<T> tList = new List<T>();
            //Type type = typeof(T);
            //string tableName = Entity.GetTableName<T>();
            //PropertyInfo[] propertiesa = type.GetProperties();
            ////select * from (select *,row_number() over (order by id) as row from users ) users where row between @PageSize*(@PageIndex-1)+1 and @PageSize*@PageIndex
            //string sql = string.Format("select * from (select *,row_number() over (order by " + Entity.GetColumnName(propertiesa[0]) + " desc) as row from {0}) {1} where row between {2} and {3}", tableName, tableName, PageSize * (NowPage - 1) + 1, NowPage * PageSize);

            //SqlDataReader reader = WinFormDbHelperSQL.ExecuteReader(sql);
            //while (reader.Read())
            //{
            //    T t = new T();
            //    PropertyInfo[] properties = type.GetProperties();
            //    foreach (PropertyInfo p in properties)
            //    {
            //        string columnName = p.Name;

            //        string PType = p.PropertyType.ToString();
            //        DBHelper.Operator(p, PType, reader[columnName], t, 0);
            //    }

            //    tList.Add(t);
            //}
            //recordCount = DBHelper.GetTableCount(tableName, "sql", Entity.GetColumnName(propertiesa[0]));
            //if (recordCount % PageSize == 0)
            //{
            //    PageCount = recordCount / PageSize;
            //}
            //else
            //{
            //    PageCount = (recordCount / PageSize) + 1;
            //}
            //reader.Close();

            //return tList;
            PageCount = 0;
            recordCount = 0;
            return null;
        }

        public List<T> GetListByPageLikeKey<T>(int PageSize, int NowPage, object obj, out int PageCount, out int recordCount) where T : class, new()
        {
            //List<T> tList = new List<T>();
            //Type type = typeof(T);
            //string tableName = Entity.GetTableName<T>();

            //Type type2 = obj.GetType();
            //PropertyInfo[] myPropertyInfo2 = type2.GetProperties();
            //string sqlparam = "";
            //foreach (PropertyInfo p in myPropertyInfo2)
            //{
            //    if (!p.Name.Equals(Entity.GetColumnName(myPropertyInfo2[0])) && p.GetValue(obj, null) != null && p.GetValue(obj, null).ToString() != "0001-1-1 0:00:00")
            //    {
            //        sqlparam += p.Name + " like'%" + p.GetValue(obj, null) + "%' and ";
            //    }
            //}
            //sqlparam = sqlparam.Substring(0, sqlparam.Length - 4);

            //string sql = string.Format("select * from (select *,row_number() over (order by " + Entity.GetColumnName(myPropertyInfo2[0]) + " desc) as row from {0} where {1}) {2} where row between {3} and {4}", tableName, sqlparam, tableName, PageSize * (NowPage - 1) + 1, NowPage * PageSize);

            //SqlDataReader reader = WinFormDbHelperSQL.ExecuteReader(sql);
            //while (reader.Read())
            //{
            //    T t = new T();
            //    PropertyInfo[] properties = type.GetProperties();
            //    foreach (PropertyInfo p in properties)
            //    {
            //        string columnName = p.Name;

            //        string PType = p.PropertyType.ToString();
            //        DBHelper.Operator(p, PType, reader[columnName], t, 0);
            //    }

            //    tList.Add(t);
            //}
            //recordCount = DBHelper.GetTableCountByKeys(tableName, sqlparam, "sql", Entity.GetColumnName(myPropertyInfo2[0]));
            //if (recordCount % PageSize == 0)
            //{
            //    PageCount = recordCount / PageSize;
            //}
            //else
            //{
            //    PageCount = (recordCount / PageSize) + 1;
            //}
            //reader.Close();

            //return tList;
            PageCount = 0;
            recordCount = 0;
            return null;
        }

        /// <summary>
        /// 按条件分页返回表里的所有记录
        /// </summary>
        /// <typeparam name="T">对象类型</typeparam>
        /// <param name="PageSize">每页信息量</param>
        /// <param name="NowPage">当前页</param>
        /// <returns></returns>
        public List<T> GetListByPageAndKeys<T>(int PageSize, int NowPage, object obj, out int PageCount, out int recordCount) where T : class, new()
        {
            //List<T> tList = new List<T>();
            //Type type = typeof(T);
            //string tableName = Entity.GetTableName<T>();

            //Type type2 = obj.GetType();
            //PropertyInfo[] myPropertyInfo2 = type2.GetProperties();
            //string sqlparam = "";
            //foreach (PropertyInfo p in myPropertyInfo2)
            //{
            //    if (!p.Name.Equals(Entity.GetColumnName(myPropertyInfo2[0])) && p.GetValue(obj, null) != null && p.GetValue(obj, null).ToString() != "0001-1-1 0:00:00")
            //    {
            //        sqlparam += p.Name + "='" + p.GetValue(obj, null) + "' and ";
            //    }
            //}
            //sqlparam = sqlparam.Substring(0, sqlparam.Length - 4);

            //string sql = string.Format("select * from (select *,row_number() over (order by " + Entity.GetColumnName(myPropertyInfo2[0]) + " desc) as row from {0} where {1}) {2} where row between {3} and {4}", tableName, sqlparam, tableName, PageSize * (NowPage - 1) + 1, NowPage * PageSize);

            //SqlDataReader reader = WinFormDbHelperSQL.ExecuteReader(sql);
            //while (reader.Read())
            //{
            //    T t = new T();
            //    PropertyInfo[] properties = type.GetProperties();
            //    foreach (PropertyInfo p in properties)
            //    {
            //        string columnName = p.Name;

            //        string PType = p.PropertyType.ToString();
            //        DBHelper.Operator(p, PType, reader[columnName], t, 0);
            //    }

            //    tList.Add(t);
            //}
            //recordCount = DBHelper.GetTableCountByKeys(tableName, sqlparam, "sql", Entity.GetColumnName(myPropertyInfo2[0]));
            //if (recordCount % PageSize == 0)
            //{
            //    PageCount = recordCount / PageSize;
            //}
            //else
            //{
            //    PageCount = (recordCount / PageSize) + 1;
            //}
            //reader.Close();

            //return tList;
            PageCount = 0;
            recordCount = 0;
            return null;
        }



        /// <summary>
        /// 查询按某条件查询的所有记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public List<T> GetAllByKey<T>(string key, string value) where T : class, new()
        {
            //List<T> tList = new List<T>();
            //Type type = typeof(T);
            //PropertyInfo[] propertie = type.GetProperties();
            //string sql = string.Format("select  * from {0} where {1}='{2}' order by " + Entity.GetColumnName(propertie[0]) + " desc", Entity.GetTableName<T>(), key, value);
            //SqlDataReader reader = WinFormDbHelperSQL.ExecuteReader(sql);
            //while (reader.Read())
            //{
            //    T t = new T();
            //    PropertyInfo[] properties = type.GetProperties();
            //    foreach (PropertyInfo p in properties)
            //    {
            //        string columnName = p.Name;
            //        string PType = p.PropertyType.ToString();
            //        DBHelper.Operator(p, PType, reader[columnName], t, 0);
            //    }
            //    tList.Add(t);
            //}
            //reader.Close();

            //return tList;
            return null;
        }

        /// <summary>
        /// 查询一条数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public object GetModel<T>(string id) where T : class, new()
        {
            //T t = new T();
            //Type type = typeof(T);
            //PropertyInfo[] propertie = type.GetProperties();
            //string sql = string.Format("select * from {0} where " + Entity.GetColumnName(propertie[0]) + "='{1}'", Entity.GetTableName<T>(), id);
            //SqlDataReader reader = WinFormDbHelperSQL.ExecuteReader(sql);
            //while (reader.Read())
            //{
            //    PropertyInfo[] properties = type.GetProperties();
            //    foreach (PropertyInfo p in properties)
            //    {
            //        string columnName = p.Name;
            //        string PType = p.PropertyType.ToString();
            //        DBHelper.Operator(p, PType, reader[columnName], t, 0);
            //    }
            //}
            //reader.Close();

            //return t;
            return null;
        }


        /// <summary>
        /// 保存(sql)
        /// </summary>
        /// <typeparam name="T"></typeparam>
        public int Insert(object obj)
        {
            int id = -1;
            if (Reflection.DAL.Entity.ValidatePassed(obj))
            {
                Type type = obj.GetType();
                PropertyInfo[] myPropertyInfo = type.GetProperties();
                string sql = string.Format("insert into {0} values(", obj.ToString().Substring(obj.ToString().LastIndexOf(".") + 1));
                foreach (PropertyInfo p in myPropertyInfo)
                {
                    if (!p.Name.Equals(myPropertyInfo[0].Name))
                    {
                        sql += "'" + p.GetValue(obj, null) + "',";
                    }
                }
                sql = sql.Substring(0, sql.Length - 1);
                sql += ");select @@identity";
                id = Convert.ToInt32(WinFormDbHelperSQL.GetSingle(sql));
            }
            return id;
        }
        /// <summary>
        /// 查询按某些条件查询的头几条记录
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <returns></returns>
        public object GetTopList<T>(int count, object obj) where T : class, new()
        {
            List<T> tList = new List<T>();
            Type type = obj.GetType();
            PropertyInfo[] myPropertyInfo = type.GetProperties();
            string sql = string.Format("select  top {0} * from {1} where ", count, obj.ToString().Substring(obj.ToString().LastIndexOf(".") + 1));
            foreach (PropertyInfo p in myPropertyInfo)
            {
                if (!p.Name.Equals(myPropertyInfo[0].Name) && p.GetValue(obj, null) != null && p.GetValue(obj, null).ToString() != "0001-1-1 0:00:00")
                {
                    sql += p.Name + "='" + p.GetValue(obj, null) + "' and ";
                }
            }
            sql = sql.Substring(0, sql.Length - 4);
            sql += " order by " + myPropertyInfo[0].Name + " desc";

            SqlDataReader reader = WinFormDbHelperSQL.ExecuteReader(sql);
            while (reader.Read())
            {
                T t = new T();
                PropertyInfo[] properties = type.GetProperties();
                foreach (PropertyInfo p in properties)
                {
                    string columnName = p.Name;

                    string PType = p.PropertyType.ToString();

                    DBHelper.Operator(p, PType, reader[columnName], t, 0);
                }

                tList.Add(t);
            }
            return tList;
        }
        /// <summary>
        /// 查询满足条件的记录是否唯一存在
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public bool IsExist(object obj)
        {
            Type type = obj.GetType();
            PropertyInfo[] myPropertyInfo = type.GetProperties();
            string sql = string.Format("select count(" + myPropertyInfo[0].Name + ") from {0} where ", obj.ToString().Substring(obj.ToString().LastIndexOf(".") + 1));
            foreach (PropertyInfo p in myPropertyInfo)
            {
                if (!p.Name.Equals(myPropertyInfo[0].Name) && p.GetValue(obj, null) != null && p.GetValue(obj, null).ToString() != "0001-1-1 0:00:00")
                {
                    sql += p.Name + "='" + p.GetValue(obj, null) + "' and ";
                }
            }
            sql = sql.Substring(0, sql.Length - 4);
            int id = Convert.ToInt32(WinFormDbHelperSQL.GetSingle(sql));

            if (id == 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 查询满足条件的记录是否存在
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public bool IsJustExist(object obj)
        {
            Type type = obj.GetType();
            PropertyInfo[] myPropertyInfo = type.GetProperties();
            string sql = string.Format("select count(" + myPropertyInfo[0].Name + ") from {0} where ", obj.ToString().Substring(obj.ToString().LastIndexOf(".") + 1));
            foreach (PropertyInfo p in myPropertyInfo)
            {
                if (!p.Name.Equals(myPropertyInfo[0].Name) && p.GetValue(obj, null) != null && p.GetValue(obj, null).ToString() != "0001-1-1 0:00:00")
                {
                    sql += p.Name + "='" + p.GetValue(obj, null) + "' and ";
                }
            }
            sql = sql.Substring(0, sql.Length - 4);
            int id = Convert.ToInt32(WinFormDbHelperSQL.GetSingle(sql));

            if (id > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 查询所有满足某些条件的数据
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public List<T> GetSomeObject<T>(object obj) where T : class, new()
        {
            List<T> tList = new List<T>();
            Type type = obj.GetType();
            PropertyInfo[] myPropertyInfo = type.GetProperties();
            string sql = string.Format("select * from {0} where ", obj.ToString().Substring(obj.ToString().LastIndexOf(".") + 1));
            foreach (PropertyInfo p in myPropertyInfo)
            {
                if (!p.Name.Equals(myPropertyInfo[0].Name) && p.GetValue(obj, null) != null && p.GetValue(obj, null).ToString() != "0001-1-1 0:00:00")
                {
                    sql += p.Name + "='" + p.GetValue(obj, null) + "' and ";
                }
            }
            sql = sql.Substring(0, sql.Length - 4);
            sql += " order by " + myPropertyInfo[0].Name + " desc";

            SqlDataReader reader = WinFormDbHelperSQL.ExecuteReader(sql);
            while (reader.Read())
            {
                T t = new T();
                PropertyInfo[] properties = type.GetProperties();
                foreach (PropertyInfo p in properties)
                {
                    string columnName = p.Name;

                    string PType = p.PropertyType.ToString();

                    DBHelper.Operator(p, PType, reader[columnName], t, 0);
                }

                tList.Add(t);
            }
            return tList;
        }
        /// <summary>
        /// 编辑一个对象并保存
        /// </summary>
        /// <param name="obj"></param>
        /// <param name="nameSpace"></param>
        public void Edit(object obj)
        {
            if (Reflection.DAL.Entity.ValidatePassed(obj))
            {
                Type type = obj.GetType();
                PropertyInfo[] myPropertyInfo = type.GetProperties();
                string sql = string.Format("update {0} set ", obj.ToString().Substring(obj.ToString().LastIndexOf(".") + 1));
                foreach (PropertyInfo p in myPropertyInfo)
                {
                    if (!p.Name.Equals(myPropertyInfo[0].Name) && p.GetValue(obj, null) != null && p.GetValue(obj, null).ToString() != "0001-1-1 0:00:00")
                    {
                        sql += p.Name + "='" + p.GetValue(obj, null) + "',";
                    }
                }
                sql = sql.Substring(0, sql.Length - 1);
                sql += " where " + myPropertyInfo[0].Name + "=" + myPropertyInfo[0].GetValue(obj, null);
                WinFormDbHelperSQL.ExecuteSql(sql);
            }
        }
        /// <summary>
        /// 删除一条记录
        /// </summary>
        /// <param name="obj">对象</param>
        public void Delete(object obj)
        {
            Type type = obj.GetType();
            PropertyInfo[] myPropertyInfo = type.GetProperties();
            string sql = string.Format("delete from {0} where " + myPropertyInfo[0].Name + "={1}", obj.ToString().Substring(obj.ToString().LastIndexOf(".") + 1), myPropertyInfo[0].GetValue(obj, null));
            WinFormDbHelperSQL.ExecuteSql(sql);
        }
        /// <summary>
        /// 删除表里的所有内容
        /// </summary>
        /// <param name="obj"></param>
        public void DeleteAll(object obj)
        {
            Type type = obj.GetType();
            PropertyInfo[] myPropertyInfo = type.GetProperties();
            string sql = string.Format("delete from {0} ", obj.ToString().Substring(obj.ToString().LastIndexOf(".") + 1));
            WinFormDbHelperSQL.ExecuteSql(sql);
        }

        /// <summary>
        /// 删除表里的几条数据（事务）
        /// </summary>
        /// <param name="obj"></param>
        public void DeleteSome(object[] obj)
        {
            List<string> al = new List<string>();
            for (int i = 0; i < obj.Length; i++)
            {
                Type type = obj[i].GetType();
                PropertyInfo[] myPropertyInfo = type.GetProperties();
                al.Add(string.Format("delete from {0} where " + myPropertyInfo[0].Name + "={1}", obj[i].ToString().Substring(obj[i].ToString().LastIndexOf(".") + 1), myPropertyInfo[0].GetValue(obj[i], null)));
            }
            WinFormDbHelperSQL.ExecuteSqlTran(al);
        }

        /// <summary>
        /// 按照数据的某些字段的值删除记录
        /// </summary>
        /// <param name="obj">对象</param>
        public void DeleteColumByKey_Value(object obj)
        {
            Type type = obj.GetType();
            PropertyInfo[] myPropertyInfo = type.GetProperties();
            string cols = "";
            string colsvalue = "";
            foreach (PropertyInfo p in myPropertyInfo)
            {
                if (p.GetValue(obj, null) != null)
                {
                    cols = p.Name;
                    colsvalue = p.GetValue(obj, null).ToString();
                }
            }

            string sql = string.Format("delete from {0} where {1}='{2}'", obj.ToString().Substring(obj.ToString().LastIndexOf(".") + 1), cols, colsvalue);
            WinFormDbHelperSQL.ExecuteSql(sql);
        }
    }
}
